//
//  DataBaseSingleTon.m
//  豆瓣2
//
//  Created by dllo on 15/11/6.
//  Copyright (c) 2015年 张鸿峰. All rights reserved.
//

#import "DataBaseSingleTon.h"

#import <sqlite3.h>

@implementation DataBaseSingleTon

+ (instancetype)shareDataBase
{
    static DataBaseSingleTon *dataBase = nil;
    
    if (dataBase == nil) {
        
        dataBase = [[DataBaseSingleTon alloc] init];
        
    }
    return dataBase;
}

//声明数据库对象
static sqlite3 *db = nil;

//打开数据库
- (void)openDBAC
{
    if (db != nil) {
        NSLog(@"数据库已经打开了");
        return;
    }
    
    NSString *sqlFile = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    NSString *sqlPath = [sqlFile stringByAppendingPathComponent:@"activity.sqlite"];
    
    NSLog(@"sqlPath = %@",sqlPath);
    
    BOOL result = sqlite3_open(sqlPath.UTF8String, &db);
    if (result == SQLITE_OK) {
        NSLog(@"数据库打开成功");
    }
    else
    {
        NSLog(@"数据库打开失败");
    }
}


// 创建活动表
- (void)createActivityTable
{
    NSString *sql = @"CREATE TABLE IF NOT EXISTS activity(number INTEGER PRIMARY KEY AUTOINCREMENT, image TEXT, title TEXT, wisher_countt TEXT, participant_countt TEXT)";
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (SQLITE_OK == result) {
        NSLog(@"创建表成功");
    } else {
        NSLog(@"创建表失败");
    }
}

//插入
- (void)insertActivity:(ActivityList *)act
{
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO activity(image, title, wisher_countt, participant_countt) VALUES('%@', '%@', '%@', '%@')",act.image, act.title, act.wisher_count, act.participant_count];
    // 执行sql语句
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (SQLITE_OK == result) {
        NSLog(@"插入成功");
    } else {
        NSLog(@"插入失败");
    }
}

//删除数据
- (void)deleteActivity:(NSString *)sql
{
    //NSString *sql = [NSString stringWithFormat:@"DELETE FROM lanou WHERE name = '%@'", name];
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"删除数据成功");
    } else {
        NSLog(@"删除数据失败");
    }
    
}


//查询
- (NSMutableArray *)selectActivity:(NSString *)sql
{
    NSMutableArray *array = [NSMutableArray array];
    //NSString *sql = @"SELECT * FROM activity";
    
    //    NSString *sql = @"SELECT * FROM activity";
    // 创建查询数据库跟随指针
    sqlite3_stmt *stmt = nil;
    // 查询前的准备工作
    // 参数3. 限制sql语句长度, -1为不限制
    // 将数据库对象db, sql语句, 跟随指针对象stmt关联到一起
    BOOL result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, nil);
    
    if (SQLITE_OK == result) {
        NSLog(@"查询准备成功");
    } else {
        NSLog(@"查询准备失败");
    }
    // 循环遍历每一行数据
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        // 取出第一列数据
        
        const unsigned char *image = sqlite3_column_text(stmt, 1);
        const unsigned char *title = sqlite3_column_text(stmt, 2);
        const unsigned char *wisher_countt = sqlite3_column_text(stmt, 3);
        const unsigned char *participant_countt = sqlite3_column_text(stmt, 4);
        
        
        // 将语言类型转成OC类型
        NSString *imageStr = [NSString stringWithUTF8String:(const char*)image];
        //NSString *begin_timeStr = [NSString stringWithUTF8String:(const char*)begin_time];
        NSString *titleStr = [NSString stringWithUTF8String:(const char*)title];
        NSString *wisher_counttStr = [NSString stringWithUTF8String:(const char*)wisher_countt];
        NSString *participant_counttStr = [NSString stringWithUTF8String:(const char*)participant_countt];
        
        
        // 创建活动对象
        ActivityList *act = [[ActivityList alloc] init];
        act.image = imageStr;
        act.title = titleStr;
        act.wisher_count = wisher_counttStr;
        act.participant_count = participant_counttStr;
        
        [array addObject:act];
        
    }
    
    return array;
    
    
}

//删除整张活动表
- (void)dropTableAC
{
    NSString *sql = @"DROP TABLE activity";
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"删除活动表成功");
    } else {
        NSLog(@"删除活动表失败");
    }
}

// 创建活动收藏列表
- (void)createActivityStoreTable
{
    NSString *sql = @"CREATE TABLE IF NOT EXISTS activityStore(number INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, userName TEXT)";
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
        if (result == SQLITE_OK) {
            
            NSLog(@"创建活动收藏表成功");
        } else {
            
            NSLog(@"创建活动收藏表失败");
        }
}

//插入活动收藏表
- (void)insertActivitysql:(NSString *)sql
{
    //NSString *sql = [NSString stringWithFormat:@"INSERT INTO activityStore(title, userName) VALUES('%@', '%@')", actS.title, actS.userName];
    // 执行sql语句
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (SQLITE_OK == result) {
        NSLog(@"活动收藏插入成功");
        
    } else {
        NSLog(@"活动收藏插入失败");
    }
}

// 查询活动收藏列表
- (NSMutableArray *)selectActivityStoreSQL:(NSString *)sql
{
    NSMutableArray *array = [NSMutableArray array];
    
    
    //    NSString *sql = @"SELECT * FROM activityStore";
    // 创建查询数据库跟随指针
    sqlite3_stmt *stmt = nil;
    
    BOOL result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, nil);
//    NSLog(@"result 666 == %d", result);
    
    // 循环遍历每一行数据
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        // 取出第一列数据
        
        const unsigned char *title1 = sqlite3_column_text(stmt, 1);
        const unsigned char *userName1 = sqlite3_column_text(stmt, 2);
        
        
        // 将语言类型转成OC类型
        NSString *title = [NSString stringWithUTF8String:(const char*)title1];
        NSString *userName = [NSString stringWithUTF8String:(const char*)userName1];
        
        
        ActivityStore *actS = [[ActivityStore alloc] init];
        actS.title = title;
        actS.userName = userName;
        
        [array addObject:actS];
        
    }
    
    return array;
    
}

//创建用户表
- (void)createUserTable
{
    NSString *sql = @"CREATE TABLE IF NOT EXISTS user(number INTEGER PRIMARY KEY AUTOINCREMENT, userName TEXT, passWord TEXT, result TEXT)";
    sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        
        NSLog(@"创建用户表成功");
    } else {
        
        NSLog(@"创建用户表失败");
    }
}

//插入用户
- (void)insertUser:(User *)user
{
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO user(userName, passWord) VALUES('%@', '%@')", user.userName, user.passWord];
    sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        
        NSLog(@"插入用户成功");
    }
    else
    {
        NSLog(@"插入用户失败");
    }
}

//查询用户
- (NSMutableArray *)selectUserSQL:(NSString *)sql
{
    NSMutableArray *array = [NSMutableArray array];
    sqlite3_stmt *stmt = nil;
    sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, nil);
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        // 取出第一列数据
        const unsigned char *userName = sqlite3_column_text(stmt, 1);
        const unsigned char *passWord = sqlite3_column_text(stmt, 2);
        const unsigned char *result = sqlite3_column_text(stmt, 3);
        
        NSString *userStr = [NSString stringWithUTF8String:(const char*)userName];
        NSString *passStr = [NSString stringWithUTF8String:(const char*)passWord];
        NSString *resultStr = [NSString stringWithUTF8String:(const char*)result];
        
        User *user = [[User alloc] init];
        user.userName = userStr;
        user.passWord = passStr;
        
        //        [array addObject:user];
        [array addObject:user.userName];
        [user release];
    }
    return array;
}


//删除用户表
- (void)dropTableUser:(NSString *)userName
{
    NSString *sql = [NSString stringWithFormat:@"DELETE FROM user WHERE userName = '%@'",userName];
    
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"删除用户表成功");
    } else {
        NSLog(@"删除用户表失败");
    }

}


//删除活动收藏表
- (void)dropActivityStoreTable:(NSString *)userName
{
    NSString *sql = [NSString stringWithFormat:@"DELETE FROM activityStore WHERE userName = '%@'",userName];

    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"删除活动收藏表成功");
    } else {
        NSLog(@"删除活动收藏表失败");
    }
}











@end
